package com.jiepos.mpos.biz.user.service;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.lang3.math.NumberUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.jiepos.mpos.common.constant.DBConstant.Table;
import com.jiepos.mpos.common.constant.SystemConstant;
import com.jiepos.mpos.common.dao.AppInsAppDao;
import com.jiepos.mpos.common.dao.AppInsInfDao;
import com.jiepos.mpos.common.entity.App_Ins_InfEntity;
import com.jiepos.mpos.common.entity.GpsEntity;
import com.jiepos.mpos.core.entity.Pagination;
import com.jiepos.mpos.core.service.BaseService;
import com.jiepos.mpos.core.util.CollectionUtil;
import com.jiepos.mpos.core.util.IDFactoryUtil;
import com.jiepos.mpos.core.util.StringUtils;

/**
 * 
 * 
 * <p>标题: —— 机构信息表服务类</p>
 * <p>模块: 通用平台</p>
 * <p>版权: Copyright (c) 2017 江苏杰博实信息技术有限公司
 * @ProjectName:  [mpos] 
 * @Package com.jiepos.mpos.biz.code.service 
 * @author 张学涛
 * @version 1.0
 * @since 2017年9月18日下午1:49:48
 * @history（历次修订内容、修订人、修订时间等）
 */
@Service
public class AppInsInfService extends BaseService {
	
	@Autowired
	private AppInsInfDao appInsInfDao;
	@Autowired
	private AppInsAppDao appInsAppDao;
	
	/**
	 * 查询机构信息列表json分页数据
	 * @param pagination
	 * @param appInsInfEntity
	 * @return
	 */
	public Map getInsInfList(HttpServletRequest request,Pagination pagination,App_Ins_InfEntity appInsInfEntity,String areaname){
		StringBuffer sb = getInitSql("t1").append(",");
		sb.append(" t1.ins_uuid as uuid, t1.ins_cd,t1.ins_cn_nm,t1.ins_type||'-'||t2.code_desc as instype,");
		sb.append("t1.area_cd||'-'||t3.area_cn_nm as areaname,t1.contact_person,t1.phone,");
		sb.append("t4.ins_cd||'-'||t4.ins_cn_nm as subinsname,t1.ins_st||'-'||t5.code_desc as insst");		
		sb.append(" from ");
		sb.append(Table.T_APP_INS_INF);
		sb.append(" t1 LEFT JOIN ");
		sb.append(Table.T_APP_CODE_LIST);
		sb.append(" t2 ON t1.ins_type = t2.code_val and t2.code_tp='INS_TYPE'");
		sb.append(" LEFT JOIN ");
		sb.append(Table.T_APP_AREA_CD);
		sb.append(" t3 on t1.area_cd=t3.area_cd");
		sb.append(" LEFT JOIN ");
		sb.append(Table.T_APP_INS_INF);
		sb.append(" t4 on t1.sup_ins_uuid=t4.ins_uuid");
		sb.append(" LEFT JOIN ");
		sb.append(Table.T_APP_CODE_LIST);
		sb.append(" t5 on t1.ins_st=t5.code_val and t5.code_tp='INS_ST'");
		sb.append(" where 1=1 ");
		List list = new ArrayList();
		if (!StringUtils.isBlank(appInsInfEntity.getIns_cd())) {
			sb.append( " and t1.ins_cd like ? " );
			list.add("%" + appInsInfEntity.getIns_cd() + "%");
		}
		if (!StringUtils.isBlank(appInsInfEntity.getIns_cn_nm())) {
			sb.append( " and t1.ins_cn_nm like ? " );
			list.add("%" + appInsInfEntity.getIns_cn_nm() + "%");
		}	
		if (!StringUtils.isBlank(appInsInfEntity.getIns_type())) {
			sb.append( " and t1.ins_type = ? " );
			list.add(appInsInfEntity.getIns_type());
		}
		if (!StringUtils.isBlank(areaname)) {
			sb.append( " and t3.area_cn_nm like ? " );
			list.add("%" + areaname + "%");
		}
		if (!StringUtils.isBlank(appInsInfEntity.getContact_person())) {
			sb.append( " and t1.contact_person like ? " );
			list.add("%" + appInsInfEntity.getContact_person() + "%");
		}
		if (!StringUtils.isBlank(appInsInfEntity.getPhone())) {
			sb.append( " and t1.phone like ? " );
			list.add("%" + appInsInfEntity.getPhone() + "%");
		}
		if (!StringUtils.isBlank(appInsInfEntity.getIns_st())) {
			sb.append( " and t1.ins_st = ? " );
			list.add(appInsInfEntity.getIns_st());
		}
		pagination.queryString = sb.toString();
		pagination = super.getInsAuthSql(request, pagination, "t1", null);
		pagination = this.getOrderSql(pagination, "t1");
		return appInsInfDao.getPaginationMap(pagination, "t1", list);	
	}

	
	/**
	 * 机构树形结果
	 * @param isOpen 是否打开，true打开，反之折叠
	 * @return
	 */
	public List<Map<String, Object>> getInsInfTree(HttpServletRequest request, String pId, String searchText){
//		StringBuffer sql = new StringBuffer();
//		sql.append("select t1.ins_uuid as ID,t1.sup_ins_uuid as PID,t1.ins_cd||'-'||t1.ins_cn_nm as NAME")
//		.append(" FROM ")
//		.append(Table.T_APP_INS_INF)
//		.append(" t1 LEFT JOIN ")
//		.append(Table.T_APP_INS_INF)
//		.append(" t2 on t1.sup_ins_uuid=t2.ins_uuid where 1=1");
//		String tempSql = this.getInsAuthSql(request, sql.toString(), "t1", null);
//		List<Map<String, Object>> ins = appInsInfDao.queryForList(getOrderSql(tempSql, "t1"), null);
		StringBuffer sql = new StringBuffer();
		if(!StringUtils.isBlank(searchText)){
			sql.append("SELECT distinct T.INS_UUID ID,T.SUP_INS_UUID PID,T.INS_CD || '-' || T.INS_CN_NM NAME")
			.append("  FROM ").append(Table.T_APP_INS_INF).append(" T")
			.append(" start with T.ins_uuid IN")
			.append("(select INS_UUID from ").append(Table.T_APP_INS_INF)
			.append(" where ")
			.append(SystemConstant.REC_ST + "!='" + SystemConstant.DEL_STATUS).append("' ")
			.append("AND LAST_OPER_IN !='" + SystemConstant.OPER_DEL).append("' ")
			.append(" and (INS_CN_NM like ? or INS_CD like ?)")
			.append(" start with ins_uuid = ? connect by prior ins_uuid = sup_ins_uuid)")
			.append(" connect by prior T.sup_ins_uuid = T.ins_uuid");
			List<String> param = new ArrayList<>();
			param.add("%" + searchText + "%");
			param.add("%" + searchText + "%");
			param.add(String.valueOf(request.getSession().getAttribute(SystemConstant.SESSION_LOGIN_INS_UUID)));
			List<Map<String, Object>> ins = appInsInfDao.queryForList(sql.toString(), param);
			return ins;
		}else{
			sql.append("SELECT T.INS_UUID ID, T.SUP_INS_UUID PID, T.INS_CD || '-' || T.INS_CN_NM NAME,")
			.append("(SELECT COUNT(1) FROM ").append(Table.T_APP_INS_INF).append(" WHERE SUP_INS_UUID = T.INS_UUID) CHILD_COUNT")
			.append("  FROM ").append(Table.T_APP_INS_INF)
			.append(" T WHERE T.SUP_INS_UUID = ?")
			.append(" AND T."+SystemConstant.REC_ST + "!='" + SystemConstant.DEL_STATUS).append("' ")
			.append(" AND T.LAST_OPER_IN !='" + SystemConstant.OPER_DEL).append("' ");
			List<String> param = new ArrayList<>();
			if(StringUtils.isBlank(pId)){// 首次加载
				sql.append(" OR T.INS_UUID = ?");
				pId = String.valueOf(request.getSession().getAttribute(SystemConstant.SESSION_LOGIN_INS_UUID));
				param.add(pId);
			}
			param.add(pId);
			List<Map<String, Object>> list = appInsInfDao.queryForList(getOrderSql(sql.toString(), "T"), param);
			for(Map<String, Object> map : list){
				if(map.get("CHILD_COUNT") != null && Integer.parseInt(map.get("CHILD_COUNT").toString()) > 0){
					map.put("isParent", true);
				}
			}
			return list;
		}
	}
	
	
	/**
	 * 获取直属或者是所有的机构数据
	 * @param request
	 * @param iD
	 * @param searchText
	 * @param  
	 * @return
	 */
	public List<Map<String, Object>> getDirectOrAllInsInfTree(HttpServletRequest request, String pId, String searchText) {
		List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
		int loginUserInsLevel = NumberUtils.toInt(StringUtils.objToString(request.getSession().getAttribute(SystemConstant.SESSION_LOGIN_INS_LEVEL)));
		/*如果是属于首级机构的查找所有的机构数据*/
		if(loginUserInsLevel == 1){
			resultList = getInsInfTree(request, pId, searchText);
		}else{/*非首级机构的只查找所在机构的直属机构(不包含自己的机构)*/
			resultList = getDirectInsInfTree(request, pId, searchText);
		}
		return resultList;
	}
	
	/**
	 * 非首级机构的只查找所在机构的直属机构(不包含自己的机构)
	 * @param request
	 * @param iD
	 * @param searchText
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getDirectInsInfTree(HttpServletRequest request, String sup_ins_uuid,
			String searchText) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT T.INS_UUID ID, T.SUP_INS_UUID PID, T.INS_CD || '-' || T.INS_CN_NM NAME ");
		sql.append("FROM ");
		sql.append(Table.T_APP_INS_INF + " T ");
		sql.append("WHERE 1=1 ");
		sql.append("AND T."+SystemConstant.REC_ST + "!='" + SystemConstant.DEL_STATUS).append("' ");
		sql.append(" AND T.LAST_OPER_IN !='" + SystemConstant.OPER_DEL).append("' ");
		List params = new ArrayList();
		if (!StringUtils.isBlank(sup_ins_uuid)) {
			sql.append("AND T.SUP_INS_UUID = ? ");
			params.add(sup_ins_uuid);
		} else {
			String cur_user_uuid = StringUtils
					.objToString(request.getSession().getAttribute(SystemConstant.SESSION_USER_UUID));
			sql.append("AND T.SUP_INS_UUID = (SELECT INS_UUID FROM " + Table.T_APP_USER_INF + " WHERE USER_UUID = ?)");
			params.add(cur_user_uuid);
		}
		if (!StringUtils.isBlank(searchText)) {
			sql.append("AND (T.INS_CN_NM like ? or T.INS_CD like ?) ");
			params.add("%" + searchText + "%");
			params.add("%" + searchText + "%");
		}
		sql.append("ORDER BY REC_ID ASC ");
		List<Map<String, Object>> ins = appInsInfDao.queryForList(sql.toString(), params);
		return ins;
	}
	
	/**
	 * 保存机构
	 * @param appInsInfEntity
	 * @param map
	 */
	public void saveInsInf(App_Ins_InfEntity appInsInfEntity,Map map){
		appInsInfEntity.setRec_upd_usr_id(String.valueOf(map.get(SystemConstant.SESSION_LOGIN_USER)));
		if (!StringUtils.isBlank(appInsInfEntity.getIns_uuid())) {
			App_Ins_InfEntity newAppInsInfEntity =appInsInfDao.findByParam("ins_uuid", appInsInfEntity.getIns_uuid());
			newAppInsInfEntity.setIns_cd(appInsInfEntity.getIns_cd());
			newAppInsInfEntity.setIns_type(appInsInfEntity.getIns_type());
			newAppInsInfEntity.setIns_cn_nm(appInsInfEntity.getIns_cn_nm());
			newAppInsInfEntity.setIns_cn_nm_abbr(appInsInfEntity.getIns_cn_nm_abbr());
			newAppInsInfEntity.setIns_en_nm(appInsInfEntity.getIns_en_nm());
			newAppInsInfEntity.setIns_en_nm_abbr(appInsInfEntity.getIns_en_nm_abbr());
			newAppInsInfEntity.setContact_person(appInsInfEntity.getContact_person());
			newAppInsInfEntity.setPhone(appInsInfEntity.getPhone());
			newAppInsInfEntity.setFax_no(appInsInfEntity.getFax_no());
			newAppInsInfEntity.setEmail(appInsInfEntity.getEmail());
			newAppInsInfEntity.setArea_cd(appInsInfEntity.getArea_cd());
			newAppInsInfEntity.setZip_cd(appInsInfEntity.getZip_cd());
			newAppInsInfEntity.setAddr(appInsInfEntity.getAddr());
			newAppInsInfEntity.setSup_ins_uuid(appInsInfEntity.getSup_ins_uuid());
			newAppInsInfEntity.setIns_st(appInsInfEntity.getIns_st());
			newAppInsInfEntity.setDescpt(appInsInfEntity.getDescpt());
			newAppInsInfEntity.setAudit_desc(appInsInfEntity.getAudit_desc());
			newAppInsInfEntity.setRec_upd_usr_id(String.valueOf(map.get(SystemConstant.SESSION_LOGIN_USER)));
			newAppInsInfEntity.setRec_upd_ts(new Timestamp(System.currentTimeMillis()));
			newAppInsInfEntity.setLast_oper_in(SystemConstant.OPER_UPDATE);
			List noChangeList = new ArrayList();
			noChangeList.add("ins_uuid");
			noChangeList.add("rec_crt_ts");
			appInsInfDao.update(newAppInsInfEntity,noChangeList);
		}else{
			appInsInfEntity.setRec_crt_ts(new Timestamp(System.currentTimeMillis()));
			appInsInfEntity.setRec_upd_ts(new Timestamp(System.currentTimeMillis()));
			appInsInfEntity.setIns_uuid(IDFactoryUtil.getUUID());			
			appInsInfEntity.setRec_st(SystemConstant.NORMAL_STATUS);
			appInsInfEntity.setLast_oper_in(SystemConstant.OPER_ADD);
			appInsInfDao.save(appInsInfEntity);
		}
	}
	
	/**
	 * 更新机构状态
	 * @param ins_uuid
	 * @param map
	 * @param flag
	 */
	public void updateInsSt(String ins_uuids,Map map,String insst){
		appInsInfDao.updateByIds("ins_uuid", ins_uuids, insst, String.valueOf(map.get(SystemConstant.SESSION_LOGIN_USER)));		
	}
	
	
	/**
	 * 根据记录id查询实体对象
	 * @param area_uuid
	 * @return
	 */
	public Map getInsInfById(HttpServletRequest request,String ins_uuid) {
		if (StringUtils.isBlank(ins_uuid) || ins_uuid.equals("undefined")) {
			return new HashMap();
		}
		StringBuilder sb = new StringBuilder();
		sb.append("select t1.ins_uuid,t1.ins_cd,t1.ins_type,t1.ins_cn_nm_abbr,t1.ins_cn_nm,t1.ins_en_nm_abbr,t1.ins_en_nm,");
		sb.append("t1.contact_person,t1.phone,t1.fax_no,t1.email,t1.area_cd,t1.area_cd||'-'||t2.area_cn_nm as areaname,t1.zip_cd,t1.addr,");
		sb.append("t1.sup_ins_uuid,t3.ins_cd||'-'||t3.ins_cn_nm as subinsname,t1.ins_st,t1.descpt,t1.audit_desc");
		sb.append( " from ");
		sb.append(Table.T_APP_INS_INF);
		sb.append(" t1 LEFT JOIN ");
		sb.append(Table.T_APP_AREA_CD);
		sb.append(" t2 on t1.area_cd=t2.area_cd");
		sb.append(" LEFT JOIN ");
		sb.append(Table.T_APP_INS_INF);
		sb.append(" t3 on t1.sup_ins_uuid=t3.ins_uuid");
		sb.append(" where t1.ins_uuid=?");
		List paramList = new ArrayList();
		paramList.add(ins_uuid);
		String tempSql = this.getInsAuthSql(request, sb.toString(), "t1", null);
		List<Map> dataList = appInsInfDao.queryForList(getOrderSql(tempSql, "t1"),paramList);
		if(dataList != null && dataList.size() > 0){
			return dataList.get(0);
		}
		return new HashMap();
	}
	
	/**
	 * 根据记录id集合删除数据
	 * @param area_uuids
	 */
	public void deleteInsInfByIds(String ins_uuids) {
		appInsInfDao.deleteByStatus("ins_uuid",ins_uuids);
	}
	
	/**
	 * 校验机构
	 * @param appInsInfEntity
	 * @return
	 */
	public String checkInsInf(App_Ins_InfEntity appInsInfEntity){
		StringBuilder sb = new StringBuilder();
		sb.append("select t1.ins_uuid, t1.ins_cd,t1.ins_cn_nm from ");		
		sb.append(Table.T_APP_INS_INF);
		sb.append(" t1 where 1=1 ");
		List list = new ArrayList();
		if (!StringUtils.isBlank(appInsInfEntity.getIns_cd())) {
			sb.append( " and t1.ins_cd = ? " );
			list.add(appInsInfEntity.getIns_cd());
		}		
		if (!StringUtils.isBlank(appInsInfEntity.getIns_uuid())) {
			sb.append( " and t1.ins_uuid != ? " );
			list.add(appInsInfEntity.getIns_uuid());
		}		
		List result1=appInsInfDao.queryForList(sb.toString(), list);
		if(result1.size()==0||result1==null){
			sb = new StringBuilder();
			sb.append("select t1.ins_uuid, t1.ins_cd,t1.ins_cn_nm from ");		
			sb.append(Table.T_APP_INS_INF);
			sb.append(" t1 where 1=1 ");
			list = new ArrayList();			
			if (!StringUtils.isBlank(appInsInfEntity.getPhone())) {
				sb.append( " and t1.phone = ? " );
				list.add(appInsInfEntity.getPhone());
			}
			if (!StringUtils.isBlank(appInsInfEntity.getIns_uuid())) {
				sb.append( " and t1.ins_uuid != ? " );
				list.add(appInsInfEntity.getIns_uuid());
			}		
			List result2=appInsInfDao.queryForList(sb.toString(), list);

			//查询会员手机号
			sb = new StringBuilder();
			sb.append("select t1.login_id from ");
			sb.append(Table.T_APP_CUSTOM_INF);
			sb.append(" t1 where 1=1 ");
			list = new ArrayList();
			if (!StringUtils.isBlank(appInsInfEntity.getPhone())) {
				sb.append( " and t1.phone = ? " );
				list.add(appInsInfEntity.getPhone());
			}
			List result3=appInsInfDao.queryForList(sb.toString(), list);
			if(result3.size()==0||result3==null){
				return SystemConstant.FAIL_3;
			}
			if(result2.size()==0||result2==null){
				return SystemConstant.SUCCESS;
			}else{
				return SystemConstant.FAIL_2;
			}
		}
		return SystemConstant.FAIL_1;
	}

	/**
	 * 根据机构代码查询机构信息
	 * @param ins_uuid 机构代码
	 * @return
	 */
	public App_Ins_InfEntity getInsInfByUuid(String ins_uuid){
		App_Ins_InfEntity entity = appInsInfDao.findByParam("ins_uuid", ins_uuid);
		return entity == null ? new App_Ins_InfEntity() : entity;
	}
	
	public List<Map<String, Object>> getInsInfByParent(String pid){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT T.INS_UUID ID, T.SUP_INS_UUID PID, T.INS_CD || '-' || T.INS_CN_NM NAME, TT.INS_UUID")
		.append("  FROM T_APP_INS_INF T")
		.append("  LEFT JOIN T_APP_INS_INF TT")
		.append("    ON T.INS_UUID = TT.SUP_INS_UUID")
		.append(" WHERE T.SUP_INS_UUID = ?");
		List<String> param = new ArrayList<>();
		param.add(pid);
		List<Map<String, Object>> list = appInsInfDao.queryForList(getOrderSql(sql.toString(), "T"), param);
		for(Map<String, Object> map : list){
			if(map.get("INS_UUID") != null){
				map.put("isParent ", true);
			}
		}
		return list;
	}
	
	/**
	 * 获取机构所在的层级
	 * @param ins_uuid
	 * @return
	 */
	public int getInsLevelByInsUUID(String ins_uuid){
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append(" SELECT COUNT(INS_UUID) count")
				.append("   FROM T_APP_INS_INF ")
				.append("  START WITH INS_UUID = ? ")
				.append(" CONNECT BY PRIOR SUP_INS_UUID = INS_UUID ");
		List<String> params = new ArrayList<>();
		params.add(ins_uuid);
		List<Map<String, Object>> list = appInsInfDao.queryForList(sqlBuf.toString(), params);
		return NumberUtils.toInt(StringUtils.objToString(list.get(0).get("COUNT")));
	}
	
	/**
	 * 根据app_id获取app_name和ins_uuid
	 * @param gpsEntity
	 * @return
	 */
	public Map getAppNameAndInsUuid(GpsEntity gpsEntity){
		StringBuffer sb = new StringBuffer();
		List list = new ArrayList<>();
		sb.append(" select app_name_app , ins_uuid from ").append(Table.T_APP_INS_APP).append(" where ");
		if(SystemConstant.APP_TYPE_IOS.equals(gpsEntity.getAppType())){
			sb.append(" ios_appid = ? and rec_st != ?");
		}else if(SystemConstant.APP_TYPE_ANDROID.equals(gpsEntity.getAppType())){
			sb.append(" android_appid = ? and rec_st != ?");
		}
		list.add(gpsEntity.getApp_id());
		list.add(SystemConstant.REC_ST_0);
		List<Map<String, Object>> r = appInsAppDao.queryForList(sb.toString(),list);
		if(!CollectionUtil.isEmpty(r)){
			return r.get(0);
		}
		return null;
		
	}

	/**
	 * 递归出所有的机构
	 * @param superUuid 父级机构
	 * @return
	 */
	public List<App_Ins_InfEntity> queryAppIns(){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT T.INS_UUID, T.INS_CD, T.SUP_INS_UUID, T.PHONE, T.INS_CN_NM, SYS_CONNECT_BY_PATH(T.INS_CD, '/') ADDR")
		.append("  FROM ").append(Table.T_APP_INS_INF).append(" T")
		.append(" WHERE T.REC_ST = '1'")
		.append(" START WITH T.SUP_INS_UUID IS NULL")
		.append(" CONNECT BY PRIOR T.INS_UUID = T.SUP_INS_UUID")
		.append(" ORDER BY T.REC_CRT_TS");
		return appInsInfDao.queryForListByParam(sql.toString(), null);
	}
	
	/**
	 * 根据父级查询出直属子级
	 * @param supUuid 父级uuid
	 * @return
	 */
	public List<App_Ins_InfEntity> queryListBySupUuid(String supUuid){
		return appInsInfDao.findListByParam("sup_ins_uuid", supUuid);
	}
	
	/**
	 * 根据某个机构的uuid递归出机构的全路径
	 * 
	 * @param ins_uuid
	 * @return
	 */
	public String queryInsTotalRoute(String ins_uuid) {
		String allPath = "";
		StringBuffer sb = new StringBuffer();
		List<String> dataList = new ArrayList<String>();
		sb.append("SELECT SYS_CONNECT_BY_PATH(T.INS_CD, '/') as allpath ").append("FROM T_APP_INS_INF T ")
				.append("WHERE T.REC_ST = '1' ").append("AND T.INS_UUID = ? ")
				.append("START WITH T.SUP_INS_UUID IS NULL ").append("CONNECT BY PRIOR T.INS_UUID = T.SUP_INS_UUID ");
		if (!StringUtils.isBlank(ins_uuid)) {
			dataList.add(ins_uuid);
		}
		List<Map<String, Object>> list = appInsInfDao.queryForList(sb.toString(), dataList);
		if (!CollectionUtil.isEmpty(list)) {
			allPath = StringUtils.objToString(list.get(0).get("allpath"));
		}
		return allPath;
	}


	/**
	 * 获取O单机构的信息
	 * @param request
	 * @param iD
	 * @param searchText
	 * @return
	 */
	public List<Map<String, Object>> getODanInsTree(HttpServletRequest request, String pId, String searchText) {
		StringBuffer sql = new StringBuffer();
		if(!StringUtils.isBlank(searchText)){
			sql.append("SELECT distinct T.INS_UUID ID,T.SUP_INS_UUID PID,T.INS_CD || '-' || T.INS_CN_NM NAME")
			.append("  FROM ").append(Table.T_APP_INS_APP).append(" IA ")
			.append(" left join ").append(Table.T_APP_INS_INF).append(" T ")
			.append("ON T.ins_uuid = IA.ins_uuid ")
			.append(" where ")
			.append("T."+SystemConstant.REC_ST + "!='" + SystemConstant.DEL_STATUS).append("' ")
			.append("AND T.LAST_OPER_IN !='" + SystemConstant.OPER_DEL).append("' ")
			.append(" and (T.INS_CN_NM like ? or T.INS_CD like ?)")
			.append(" start with T.ins_uuid IN")
			.append("(select INS_UUID from ").append(Table.T_APP_INS_INF)
			.append(" where ")
			.append(SystemConstant.REC_ST + "!='" + SystemConstant.DEL_STATUS).append("' ")
			.append("AND LAST_OPER_IN !='" + SystemConstant.OPER_DEL).append("' ")
			.append(" and (INS_CN_NM like ? or INS_CD like ?)")
			.append(" start with ins_uuid = ? connect by prior ins_uuid = sup_ins_uuid)")
			.append(" connect by prior T.ins_uuid = T.sup_ins_uuid");
			List<String> param = new ArrayList<>();
			param.add("%" + searchText + "%");
			param.add("%" + searchText + "%");
			param.add("%" + searchText + "%");
			param.add("%" + searchText + "%");
			param.add(String.valueOf(request.getSession().getAttribute(SystemConstant.SESSION_LOGIN_INS_UUID)));
			List<Map<String, Object>> ins = appInsInfDao.queryForList(sql.toString(), param);
			return ins;
		}else{
			sql.append("SELECT distinct  T.INS_UUID ID, T.SUP_INS_UUID PID, T.INS_CD || '-' || T.INS_CN_NM NAME")
			.append("  FROM ")
			.append(Table.T_APP_INS_APP).append(" T1 ")
			.append("LEFT JOIN ")
			.append(Table.T_APP_INS_INF)
			.append(" T ON T1.ins_uuid = t.ins_uuid WHERE ")
			.append(" T."+SystemConstant.REC_ST + "!='" + SystemConstant.DEL_STATUS).append("' ")
			.append(" AND T.LAST_OPER_IN !='" + SystemConstant.OPER_DEL).append("' ")
			.append(" start with T.ins_uuid IN")
			.append("(select INS_UUID from ").append(Table.T_APP_INS_INF)
			.append(" where ")
			.append(SystemConstant.REC_ST + "!='" + SystemConstant.DEL_STATUS).append("' ")
			.append("AND LAST_OPER_IN !='" + SystemConstant.OPER_DEL).append("' ")
			.append(" start with ins_uuid = ? connect by prior ins_uuid = sup_ins_uuid)")
			.append(" connect by prior T.ins_uuid = T.sup_ins_uuid");
			List<String> param = new ArrayList<>();
			if(StringUtils.isBlank(pId)){// 首次加载
				pId = String.valueOf(request.getSession().getAttribute(SystemConstant.SESSION_LOGIN_INS_UUID));
				param.add(pId);
			}else{
				param.add(pId);
			}
			List<Map<String, Object>> list = appInsInfDao.queryForList(sql.toString(), param);
			return list;
		}
	}

	/**
	 * 根据机构app uuid得到机构号
	 * @param ins_app_uuid
	 * @return
	 */
	public String getInsCDByInsAppUUID(String ins_app_uuid){
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append(" SELECT B.INS_CD ")
				.append("   FROM (SELECT INS_UUID ")
				.append("           FROM T_APP_INS_APP ")
				.append("          WHERE INS_APP_UUID = ? ")
				.append("            AND REC_ST != '0') A ")
				.append("   LEFT JOIN T_APP_INS_INF B ")
				.append("     ON B.INS_UUID = A.INS_UUID ");
		List params = new ArrayList();
		params.add(ins_app_uuid);
		List<Map<String, Object>> result = appInsInfDao.queryForList(sqlBuf.toString(), params);
		if(result != null && result.size()>0 ){
			return StringUtils.objToString(result.get(0).get("INS_CD"));
		}else{
			return "-1";
		}
	}
}
